SQL Server must recover to a known state that is verifiable.

Overview

Finding ID	Version	Rule ID	IA Controls	Severity
V-40932	SQL2-00-017500	SV-53286r1_rule		High

Description
Application recovery and reconstitution constitutes executing an information system contingency plan comprised of activities that restore essential missions and business functions. SQL Server utilizes transaction-based processing and is a good example of information systems that are transaction-based. Transaction rollback and transaction journaling are examples of mechanisms supporting transaction recovery. SQL Server may be vulnerable to use of compromised data or other critical files during recovery. Use of compromised files could introduce maliciously altered application code, relaxed security settings, or loss of data integrity. SQL Server mechanisms must be configured to protect all files that could compromise the system or its data during a SQL Server recovery.

Description

Application recovery and reconstitution constitutes executing an information system contingency plan comprised of activities that restore essential missions and business functions. SQL Server utilizes transaction-based processing and is a good example of information systems that are transaction-based. Transaction rollback and transaction journaling are examples of mechanisms supporting transaction recovery. SQL Server may be vulnerable to use of compromised data or other critical files during recovery. Use of compromised files could introduce maliciously altered application code, relaxed security settings, or loss of data integrity. SQL Server mechanisms must be configured to protect all files that could compromise the system or its data during a SQL Server recovery.

STIG	Date
Microsoft SQL Server 2012 Database Instance Security Technical Implementation Guide	2014-01-05

Details

Check Text ( C-47587r2_chk )
Obtain the SQL Server recovery procedures and technical system features to determine if mechanisms exist and are in place to specify use of trusted files during SQL Server recovery. If recovery procedures do not exist or are not sufficient to ensure recovery is done in a secure and verifiable manner, this is a finding. Check the configurations of all transaction log files that are enabled by running the following SQL Server query: EXEC sp_MSforeachdb ' SELECT ''?'' AS ''database name'' , name AS ''log file name'' , physical_name AS ''log file location and name'' , state_desc , size , max_size , growth , is_percent_growth FROM ?.sys.database_files WHERE type_desc = ''LOG'' AND state = 0 ' If any transaction log files are not configured correctly for size, max_size, and growth to log sufficient transaction information, this is a finding.

Check Text ( C-47587r2_chk )

Obtain the SQL Server recovery procedures and technical system features to determine if mechanisms exist and are in place to specify use of trusted files during SQL Server recovery.

If recovery procedures do not exist or are not sufficient to ensure recovery is done in a secure and verifiable manner, this is a finding.

Check the configurations of all transaction log files that are enabled by running the following SQL Server query:

EXEC sp_MSforeachdb '
SELECT ''?'' AS ''database name''
, name AS ''log file name''
, physical_name AS ''log file location and name''
, state_desc
, size
, max_size
, growth
, is_percent_growth
FROM ?.sys.database_files
WHERE type_desc = ''LOG''
AND state = 0
'

If any transaction log files are not configured correctly for size, max_size, and growth to log sufficient transaction information, this is a finding.

Fix Text (F-46214r2_fix)
Modify system log file: Navigate to SQL Server Management Studio >> Object Explorer >> <'SQL Server name'> >> Databases >> System Databases >> right click on <'system database name'> >> Properties >> Files. OR Modify user-defined log file: Navigate to SQL Server Management Studio >> Object Explorer >> <'SQL Server name'> >> Databases >> right click on <'user-defined database name'> >> Properties >> Files. THEN Add database transaction log file if one does not exist. To modify Initial Size (MB) click on "Initial Size (MB)" then edit value. To modify Autogrowth click on the "Autogrowth / Maxsize" button that is in Log record, choose "In Percent" or "In Megabytes", enter value, and then click OK. To modify Maximum File Size click on the "Autogrowth/Maxsize" button that is in Log record, choose "Limited to (MB)" or "Unlimited", enter value, and then click OK.

Fix Text (F-46214r2_fix)

Modify system log file:
Navigate to SQL Server Management Studio >> Object Explorer >> <'SQL Server name'> >> Databases >> System Databases >> right click on <'system database name'> >> Properties >> Files.

OR

Modify user-defined log file:
Navigate to SQL Server Management Studio >> Object Explorer >> <'SQL Server name'> >> Databases >> right click on <'user-defined database name'> >> Properties >> Files.

THEN

Add database transaction log file if one does not exist.

To modify Initial Size (MB) click on "Initial Size (MB)" then edit value.

To modify Autogrowth click on the "Autogrowth / Maxsize" button that is in Log record, choose "In Percent" or "In Megabytes", enter value, and then click OK.

To modify Maximum File Size click on the "Autogrowth/Maxsize" button that is in Log record, choose "Limited to (MB)" or "Unlimited", enter value, and then click OK.